package com.anda.logistics.dao.impl;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Component;

import com.anda.logistics.dao.TruckInfoDao;
import com.anda.logistics.map.util.CommUtil;
import com.anda.logistics.model.TruckInfo;
import com.anda.logistics.util.JQGridPager;
import com.anda.logistics.util.JQGridPager.SearchFilter;
import com.anda.logistics.util.JQGridPager.SearchFilterRules;
import com.anda.logistics.util.QueryFilter;
import com.anda.logistics.util.QueryFilter.SqlFilter;
import com.anda.logistics.vo.JsonReturnVo;
import com.anda.logistics.vo.TruckInfoVo;

@Component
public class TruckInfoDaoImpl extends AndaBaseSuperDao implements TruckInfoDao{
	
	@Override
	public void save(Object entity) {
		sessionFactory.getCurrentSession().save(entity);
	}

	@Override
	public List<TruckInfo> getAll() {
		return sessionFactory.getCurrentSession()
				.createQuery("from TruckInfo order by id")
				.list();
	}
	
	@Override
	public TruckInfo getOne(long id){
		return (TruckInfo) this.sessionFactory.getCurrentSession().get(TruckInfo.class, id);
	}
	
	@Override
	public void deleteOne(int id){
		TruckInfo t = new TruckInfo(id);
		sessionFactory.getCurrentSession().delete(t);
	}

	@Override
	public void update(TruckInfo truckInfo) {
		sessionFactory.getCurrentSession().update(truckInfo);		
	}
	
	private String getPagedWhereClause(JQGridPager pager, String state){
		StringBuffer sb = new StringBuffer();
		
		SearchFilter filters = pager.getFiltersObj();
		if ( filters != null){
			SearchFilterRules[] rules = filters.getRules();
			for(int i=0; i< rules.length; i++){
				if (i==0){
					sb.append(" where ");
				} else {
					sb.append(filters.getGroupOp());
				}
				if (rules[i].getField().endsWith("Time")){
					sb.append(" DATE(").append(rules[i].getField()).append(")").append(rules[i].getMapedOp()) .append(" ? ");
				} else {
					sb.append(" ").append(rules[i].getField()).append(rules[i].getMapedOp()) .append(" ? ");
				}
			}
		}
		if (state != null) {
			if (sb.length() > 0) {
				sb.append(" and truckState = ? ");
			} else {
				sb.append(" where truckState = ? ");
			}
		}
		return sb.toString();
	}
	
	private void setPagedParameterSql(Query q,JQGridPager pager,String state){
		SearchFilter filters = pager.getFiltersObj();
		int i=0;
		if ( filters != null){
			SearchFilterRules[] rules = filters.getRules();
			for(; i< rules.length; i++){
				q.setString(i, rules[i].getMapedData(true) );				
			}
		}
		if (state != null) {
			q.setInteger(i, Integer.parseInt(state));
		}
	}
	
	@Override
	public List<TruckInfo> getPagerList(JQGridPager pager, String state) {
		StringBuffer queryStr = new StringBuffer();
		queryStr.append("from TruckInfo ")
			.append(getPagedWhereClause(pager , state))
			.append(" order by id desc ");
		
		Query q = sessionFactory.getCurrentSession()
			.createQuery(queryStr.toString());
		setPagedParameterSql(q,pager,state);
		
		q.setFirstResult((pager.getPage() -1) * pager.getRows());
		q.setMaxResults(pager.getRows());
		return q.list();
	}

	@Override
	public long getRecords() {
		return (Long) ( sessionFactory.getCurrentSession().createQuery("select count(*) from TruckInfo t ").uniqueResult());
	}
	
	@Override
	public long getPagedRecords(JQGridPager pager , String state){
		StringBuffer queryString = new StringBuffer();
		queryString.append("select count(*) from TruckInfo ");
		queryString.append(getPagedWhereClause(pager, state));
		Query q = sessionFactory.getCurrentSession()
				.createQuery(queryString.toString());
		setPagedParameterSql(q,pager,state);
		return (Long) ( q.uniqueResult());
	}
	
	@Override
	public List getTruckCountByProvince(){
		return
				sessionFactory.getCurrentSession()
				.createSQLQuery(
					new StringBuilder()
						.append("select b.sortnum as \"sortnum\",b.codeValue as \"codeValue\",b.codeText as \"provinceName\" ,count(*) as \"count\" ")
						.append("  from truckinfo as a, systemcode  as b ")
						.append(" where a.locProvince = b.codeValue ")
						.append("   and b.codeName = 'regioncode' ")
						.append("   and trim(coalesce(b.parentValue,'')) = '' ")
						.append(" group by b.sortnum,b.codeValue,b.codeText ")
						.append(" order by b.sortnum,b.codeValue" )
						.toString())
				.setResultTransformer(Transformers.TO_LIST)
				.list();
	}
	public static void main(String args[])
	{
		String s= "%E6%9D%8E%E5%B0%8F%E9%BE%99";
		s="%E7%8E%8B";
		try {
			s = URLDecoder.decode(s,"UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		System.out.println(s);
	}

	@Override
	public int updateAuditState(String id) {
		return sessionFactory.getCurrentSession()
			.createQuery("update TruckInfo set truckState = :truckState where id = :id ")
			.setInteger("truckState", 1 )
			.setInteger("id", Integer.parseInt(id))
			.executeUpdate();
	}

	@Override
	public void updateState(TruckInfo truckInfo, int state) {
		this.sessionFactory.getCurrentSession()
		.createQuery("update TruckInfo set truckState = :truckState where id = :id ")
		.setInteger("truckState", state )
		.setLong("id", truckInfo.getId())
		.executeUpdate();		
	}

	@Override
	public TruckInfo getOneByMobileNo(String sMobileNo) {
		return (TruckInfo) sessionFactory.getCurrentSession()
			.createQuery("from TruckInfo where mobileNo = :mobileNo ")
			.setString("mobileNo", sMobileNo)
			.setMaxResults(1)
			.uniqueResult();
	}

	@Override
	public void delete(TruckInfo ti) {
		sessionFactory.getCurrentSession().delete(ti);		
	}

	@Override
	public JsonReturnVo getListByQuery(QueryFilter qf) {
		JsonReturnVo vo = new JsonReturnVo();
		vo.setStatus("OK");
		StringBuffer sql = new StringBuffer();
		StringBuffer where = new StringBuffer(" a.truckState > 0 ");
		if (qf != null ) {
			if ( qf.getIds() != null && qf.getIds().length > 0){
				where.append( " and a.id not in (:ids) ");
			}
			if (qf.getList() != null){
				for (SqlFilter sf: qf.getList()){
					if (where.length() > 0){
						where.append(" and ");
					}
					where.append(sf.isPre()?" a.":" ").append(sf.getSql());
				}
			}
		}
		sql.append("select a.id,a.truckBrand,a.truckType,a.capacity,a.plateNo,a.truckLength")
			.append("     ,a.truckVolumn,a.bodyStruc,a.locProvince,a.locCity,a.locRegion,a.drivingLicense")
			.append("     ,a.runningToken,a.driverName,a.driverSex,a.mobileNo,a.licenseType,a.freqLine")
			.append("     ,a.makeTime,a.modifyTime,a.truckState,a.createUserId,a.createUserName,a.auditUserId")
			.append("     ,a.auditUserName,a.auditTime")
			.append("     ,b.lat,b.lng,b.getTime,b.cityCode,b.addressDetail,b.locationDesc")
	        .append("  from TruckInfo a left join DriverLocationRt b ")
	        .append("    on a.id = b.id ")
	        .append(where.length()>0?"   where ":"" ).append(where)
	        .append(" order by a.id desc");
		Query query = sessionFactory.getCurrentSession()
				.createSQLQuery(sql.toString())
				.addScalar("id", StandardBasicTypes.INTEGER)
				.addScalar("truckBrand", StandardBasicTypes.STRING)
				.addScalar("truckType", StandardBasicTypes.STRING)
				.addScalar("capacity", StandardBasicTypes.STRING)
				.addScalar("plateNo", StandardBasicTypes.STRING)
				.addScalar("truckLength", StandardBasicTypes.STRING)
				.addScalar("truckVolumn", StandardBasicTypes.FLOAT)
				.addScalar("bodyStruc", StandardBasicTypes.STRING)
				.addScalar("locProvince", StandardBasicTypes.STRING)
				.addScalar("locCity", StandardBasicTypes.STRING)
				.addScalar("locRegion", StandardBasicTypes.STRING)
				.addScalar("drivingLicense", StandardBasicTypes.STRING)
				.addScalar("runningToken", StandardBasicTypes.STRING)
				.addScalar("driverName", StandardBasicTypes.STRING)
				.addScalar("driverSex", StandardBasicTypes.INTEGER)
				.addScalar("mobileNo", StandardBasicTypes.STRING)
				.addScalar("licenseType", StandardBasicTypes.STRING)
				.addScalar("freqLine", StandardBasicTypes.STRING)
				.addScalar("makeTime", StandardBasicTypes.TIMESTAMP)
				.addScalar("modifyTime", StandardBasicTypes.TIMESTAMP)
				.addScalar("truckState", StandardBasicTypes.INTEGER)
				.addScalar("createUserId", StandardBasicTypes.LONG)
				.addScalar("createUserName", StandardBasicTypes.STRING)
				.addScalar("auditUserId", StandardBasicTypes.LONG)
				.addScalar("auditUserName", StandardBasicTypes.STRING)
				.addScalar("auditTime", StandardBasicTypes.TIMESTAMP)
				.addScalar("lat", StandardBasicTypes.DOUBLE)
				.addScalar("lng", StandardBasicTypes.DOUBLE)
				.addScalar("getTime", StandardBasicTypes.TIMESTAMP)
				.addScalar("cityCode", StandardBasicTypes.STRING)
				.addScalar("addressDetail", StandardBasicTypes.STRING)
				.addScalar("locationDesc", StandardBasicTypes.STRING)
				.setMaxResults(10)
				.setResultTransformer(Transformers.aliasToBean(TruckInfoVo.class));
		if (qf != null){
			if (qf.getIds() != null && qf.getIds().length > 0){
				query.setParameterList("ids", CommUtil.getLongs(qf.getIds()));
			}
			qf.setSqlFilterValues(query);
		}
		vo.setData(query.list());
		
		System.out.println(sql);
		sql.setLength(0);
		sql.append("select count(*) from TruckInfo a ").append(where.length()>0?" where ":"").append(where);
		System.out.println(sql);
		query = sessionFactory.getCurrentSession()
				.createQuery(sql.toString());
		if (qf != null){
			if (qf.getIds() != null && qf.getIds().length > 0){
				query.setParameterList("ids", CommUtil.getLongs(qf.getIds()));
			}
			qf.setSqlFilterValues(query);
		}
		Number n = (Number) query.uniqueResult();
		vo.setCount(n.intValue());
		return vo;
	}
}
